<?php
class SysPostgreSql {
	public $config = array();
	public $conn = NULL;
	public $sql = '';
	public $recordset;
	public $result;
	public $oid;	// last insert id
	
	public function __construct() {
		
	}
	
	/**
	 * use pg_pcconnect()
	 * @param $host
	 * @param $port
	 * @param $dbname
	 * @param $user
	 * @param $pass
	 * @return $conn
	 */
	function connect () {
		$host = $this->config['host'];
		$port = $this->config['port'];
		$user = $this->config['username'];
		$pass = $this->config['password'];
		$dbname = $this->config['dbname'];
		$str = "host={$host} port={$port} dbname={$dbname} user={$user} password={$pass}";
		
		if (!function_exists('pg_pconnect')) $this->execeptionError('Do not support database postgres');
		
		$this->conn = @pg_pconnect($str) OR $this->execeptionError(pg_last_error($this->conn));
	}
	
	function getConn () {
		if ($this->conn == NULL) $this->connect();
		
		return $this->conn;
	}
	
	/**
	 * @desc use SELECT
	 * @return Recordset
	 */
	public function query ($sql='') {
		$this->sql = $sql;
		if ($this->conn == NULL) $this->connect();
		$this->recordset = @pg_query($sql) OR $this->execeptionError(pg_last_error($this->conn));
		return $this->recordset;
	}
	
	/**
	 * @desc use INSERT,DELETE,UPDATE
	 * @return:
	 * 		- ROLLBACK if false
	 * 		- COMMIT if true
	 */
	public function queryRollback ($sql='') {
		$this->sql = $sql;
		if ($this->conn == NULL) $this->connect();
		
		// Verify and end the transaction as appropriate.
		pg_query("BEGIN WORK");
		$res = @pg_query($sql) OR $this->execeptionError(pg_last_error($this->conn), 'ROLLBACK');
		pg_query("COMMIT");
		
		$this->recordset = $res;
		return $this->recordset;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(0=>val1,1=>val2,...), 1=>array2(0=>val1,1=>val2,...),...)
	 */
	public function fetch ($sql) {
		$this->query($sql);
		$this->result = array();
		while ($row = pg_fetch_row($this->recordset)) {
			$this->result[] = $row;
		}
		return $this->result;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(col1,col2,...), 1=>array2(col1, col2,...),...)
	 */
	public function fetchAll ($sql) {
		$this->query($sql);
		$this->result = pg_fetch_all($this->recordset);

		return $this->result;
	}
	
	/**
	 * @desc get one first of rows
	 * @return array record
	 */
	public function selectOne ($sql) {
		$sql.= "\n LIMIT 1";
		$this->query($sql);
		$this->result = pg_fetch_assoc($this->recordset);
		return $this->result;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @param string $key key of return result
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * @return rows: array(
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 	)
	 */
	public function select ($sql='',$key='',$page_record=NULL,$page_no=NULL) {
		if ($key == '') {
			$this->query($sql);
			$this->result = pg_fetch_all($this->recordset);
		} else {
			$this->query($sql);
			$this->result = array();
			while ($row = pg_fetch_assoc($this->recordset)) {
				if (!isset($row[$key])) $this->execeptionError('Do not exist key ['.$key.']');
				
				$this->result[$row[$key]][] = $row;
			}
		}
		return $this->result;
	}
	
	/**
	 * @return nums row
	 */
	public function getNumRows ($res=NULL) {
		if (is_null($res)) $res = $this->recordset;
		if ($res == '') return 0;
		
		return pg_num_rows($res);
	}
	
	
	/**
	 * @desc insert one record
	 * @return array new record id OR false
	 */
	public function insert ($sql='', $key_name='') {
		if ($key_name != '') {
			$sql.= "\n RETURNING {$key_name}";
		}
		
		$res = $this->queryRollback($sql);
		$oid = pg_fetch_assoc($res);
		if (!is_null($oid)) {
			$this->oid = $oid;
			return $this->oid;
		}
		
		if ($this->recordset) return true;
		return false;
	}
	
	/**
	 * @return last insert id
	 */
	public function getLastId () {
		return $this->oid;
	}
	
	/**
	 * @return true|false
	 */
	public function update ($sql,$limit='') {
		/*if ($limit != '') {
			$sql.= "\n LIMIT {$limit}";
		}*/
		$this->queryRollback($sql);
		if ($this->recordset) return true;
		
		return false;
	}
	
	public function delete ($sql, $limit='') {
		/*if ($limit != '') {
			$sql.= "\n LIMIT {$limit}";
		}*/
		$this->queryRollback($sql);
		if ($this->recordset) return true;
		
		return false;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(0=>val1,1=>val2,...), 1=>array2(0=>val1,1=>val2,...),...)
	 */
	public function getFetch ($res=NULL) {
		if (!$res) return ;
		
		$this->result = array();
		while ($row = pg_fetch_row($res)) {
			$this->result[] = $row;
		}
		return $this->result;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(col1,col2,...), 1=>array2(col1, col2,...),...)
	 */
	public function getFetchAll ($res=NULL) {
		if (!$res) return ;
		
		$this->result = pg_fetch_all($res);

		return $this->result;
	}
	
	/**
	 * @desc get one first of rows
	 * @return array record
	 */
	public function getSelectOne ($res=NULL) {
		if (!$res) return ;
		
		$this->result = pg_fetch_assoc($res);
		return $this->result;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @param string $key key of return result
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * @return rows: array(
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 	)
	 */
	public function getSelect ($res=NULL,$key='') {
		if (!$res) return ;
		
		if ($key == '') {
			$this->result = pg_fetch_all($res);
		} else {
			$this->result = array();
			while ($row = pg_fetch_assoc($res)) {
				if (!isset($row[$key])) $this->exceptionError('Do not exist key ['.$key.']');
				
				$this->result[$row[$key]][] = $row;
			}
		}
		return $this->result;
	}
	
	
	/**
	 * @return print error
	 */
	public function exceptionError ($msg, $act='') {
		if ($act == 'ROLLBACK') {
			 @pg_query("ROLLBACK");
		}
		throw new SysExceptionModel($msg);
	}
	
	/* Prismy_47  2012-06-13 mod Start */
	/**
	 * @desc close connection
	 */
	public function close () {
		if ($this->conn) {
			@pg_close($this->conn); // Close this connection
		} 
	}
	
	/**
	 * @desc close connection
	 */
	public function __destruct () {
		if ($this->conn) {
			//@pg_close($this->conn); // Close this connection
		} 
	}
	/* Prismy_47  2012-06-13 mod End */
}